package com.whstudio.util.mvc.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.w3c.dom.CDATASection;
import org.w3c.dom.Element;

import com.whstudio.common.memory.pool.ConnectionPool;
import com.whstudio.util.string.StrUtil;
import com.whstudio.util.xml.CDocument;
import com.whstudio.util.xml.IDocument;

public class AbstractDAO implements IDAO {
	private final static ConnectionPool cp = ConnectionPool.getPool();

	@Override
	public void endConnection(ResultSet rs, Statement stmt, Connection conn)
			throws SQLException {
		if (rs != null)
			rs.close();
		if (stmt != null)
			stmt.close();
		cp.close(conn);
	}

	@Override
	public Connection getConnection() {
		// cp.viewAllStatus();
		return cp.getObject();
	}

	protected String getCDATAResult(ResultSet rs, String nodeName)
			throws SQLException {
		StringBuffer result = new StringBuffer();
		if (StrUtil.noe(nodeName)) {
			nodeName = "r";
		}
		result.append("<" + nodeName + ">");
		ResultSetMetaData rsmd = rs.getMetaData();
		int colCount = rsmd.getColumnCount();
		while (rs.next()) {
			result.append("<row>");
			for (int i = 1; i < colCount + 1; i++) {
				String columnName = rsmd.getColumnName(i);
				result.append("<").append(columnName).append("><![CDATA[")
						.append(StrUtil.noe(rs.getString(columnName), ""))
						.append("]]></").append(columnName).append(">");
			}
			result.append("</row>");
		}
		result.append("</" + nodeName + ">");
		return result.toString();
	}
	
	/**
	 * 从结果集中构建XML文档
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected IDocument getResultDocument(ResultSet rs)
			throws SQLException {
		IDocument document=CDocument.getIDocWithRoot("invoice");
		ResultSetMetaData rsmd = rs.getMetaData();
		Element rootElement=document.getDocumentElement();
		rootElement.setAttribute("name", rsmd.getTableName(1));
		
		int colCount = rsmd.getColumnCount();
		while (rs.next()) {
			Element listElement=document.createElement("list");
			for (int i = 1; i < colCount + 1; i++) {
				String columnName = rsmd.getColumnName(i);
				CDATASection section=document.createCDATASection(StrUtil.noe(rs.getString(columnName), ""));
				Element itemElement=document.createElement("item");
				itemElement.setAttribute("name", columnName);
				itemElement.appendChild(section);
				listElement.appendChild(itemElement);
			}
			rootElement.appendChild(listElement);
		}
		return document;
	}
	
	/**
	 * 删除表中所有数据
	 * @return
	 * @throws SQLException
	 */
	protected Integer deleteAllDataOfTable(String tableName)  throws SQLException{
		String sqlString="delete from "+tableName;
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		Integer resultInteger=statement.executeUpdate();
		endConnection(null, statement, connection);
		return resultInteger;
	}
	
	/**
	 * 获取表中数据量
	 * @return
	 * @throws SQLException
	 */
	protected Integer getTableRowCount(String tableName) throws SQLException{
		String sqlString="select count(*) from "+tableName;
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		ResultSet set=statement.executeQuery();
		Integer rowCountInteger=null;
		if(set.next()){
			rowCountInteger=set.getInt(1);
		}
		endConnection(set, statement, connection);
		return rowCountInteger;
	}

	/**
	 * 更新表中的一个字段
	 * @param key
	 * @param keyColumnName
	 * @param tableName
	 * @param columnName
	 * @param valueString
	 * @return
	 * @throws SQLException
	 */
	protected int editTableColumn(Integer key,String keyColumnName,String tableName,String columnName,String valueString) throws SQLException{
		String sqlString="update "+tableName+" set "+columnName+"=? where "+keyColumnName+"=?";
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		statement.setString(1, valueString);
		statement.setInt(2, key);
		int result=statement.executeUpdate();
		endConnection(null, statement, connection);
		return result;
	}
	
	/**
	 * 根据主键查询一个字段的值
	 * @param key
	 * @param keyColumnName
	 * @param tableName
	 * @param columnName
	 * @return
	 * @throws SQLException
	 */
	protected String queryColumnFromTable(Integer key,String keyColumnName,String tableName,String columnName) throws SQLException{
		String sqlString="select "+columnName+" from "+tableName+" where "+keyColumnName+"=?";
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		statement.setInt(1, key);
		ResultSet set=statement.executeQuery();
		String columnValueString=null;
		if(set.next()){
			columnValueString=set.getString(columnName);
		}
		endConnection(set, statement, connection);
		return columnValueString;
	}
	
	/**
	 * 根据主键查询记录
	 * @param key
	 * @param keyColumnName
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	protected IDocument queryRowFromTable(Integer key,String keyColumnName,String tableName) throws SQLException{
		return queryRowFromTable(key,java.sql.Types.INTEGER, keyColumnName, tableName);
	}
	
	/**
	 * 根据某个字段查询记录
	 * @param keywords
	 * @param keywordsColumnName
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	protected IDocument queryRowFromTable(Object keywords,Integer targetSqlType,String keywordsColumnName,String tableName) throws SQLException{
		String sqlString="select * from "+tableName+" where "+keywordsColumnName+"=?";
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		statement.setObject(1, keywords, targetSqlType);
		ResultSet set=statement.executeQuery();
		IDocument invoice=getResultDocument(set);
		endConnection(set, statement, connection);
		return invoice;
	}
	
	/**
	 * 获取一个表中所有记录
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	protected IDocument queryAllRowFromTable(String tableName) throws SQLException{
		String sqlString="select * from "+tableName;
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		ResultSet set=statement.executeQuery();
		IDocument invoice=getResultDocument(set);
		endConnection(set, statement, connection);
		return invoice;
	}
	
	/**
	 * 根据SQL语句返回查询结果
	 * 仅限无参数查询
	 * @param sqlString
	 * @return
	 * @throws SQLException
	 */
	protected IDocument getResultDocumentBySql(String sqlString) throws SQLException {
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		ResultSet set=statement.executeQuery();
		IDocument invoice=getResultDocument(set);
		endConnection(set, statement, connection);
		return invoice;
	}
	
	/**
	 * 获取当前时间
	 * @return
	 * @throws SQLException
	 */
	public Date getNowDate() throws SQLException{
		String sqlString="select now()";
		Connection connection=getConnection();
		PreparedStatement statement=connection.prepareStatement(sqlString);
		ResultSet set=statement.executeQuery();
		Date nowDate=null;
		if(set.next()){
			nowDate=set.getDate(1);
		}
		endConnection(set, statement, connection);
		return nowDate;
	}
	
}